import pymysql


class MysqlHelper:
    def __init__(self,host,port,user,password,database):
        # 连接数据库
        self.conn =pymysql.connect(host=host,
                                     user=user,
                                   port= int(port),
                                     password=password,
                                     database=database,
                                     cursorclass=pymysql.cursors.DictCursor)
        self.database = database

    def get_databases(self):
        sql = "SELECT SCHEMA_NAME as name FROM INFORMATION_SCHEMA.SCHEMATA;"
        cur = self.conn.cursor()
        # 执行SQL查询
        cur.execute(sql)
        rows = cur.fetchall()
        cur.close()
        return rows

    def get_tables(self):
        # 创建连接
        cur = self.conn.cursor()
        # 执行SQL查询
        cur.execute("SHOW TABLES;")
        # 获取查询结果
        rows = cur.fetchall()
        cur.close()
        data_list = []
        # print(rows)
        for row in rows:
            data_list.append({"name":row[f'Tables_in_{self.database}']})
        return data_list

    def get_colum_list(self,name):
        # 创建连接
        cur = self.conn.cursor()
        # 执行SQL查询
        cur.execute(f"""SELECT  COLUMN_NAME as name ,IS_NULLABLE as nullable ,DATA_TYPE as col_type, COLUMN_DEFAULT AS defval,COLUMN_KEY as `pk`,
                    CHARACTER_OCTET_LENGTH str_len,NUMERIC_PRECISION int_len,NUMERIC_SCALE as digits,COLUMN_COMMENT as `desc` 
                     FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{name}'
                    """)
        # 获取查询结果
        rows = cur.fetchall()
        cur.close()
        data_list = []
        for row in rows:
            len =  row['str_len'] if row['str_len'] else row['int_len']
            if row['digits']:
                len = f"{len},{row['digits']}"

            data = {
                "col_type": row["col_type"],
                "defval": row["defval"],
                "desc": row["desc"],
                "len": len,
                "name": row["name"],
                "nullable": 0 if row["nullable"]  == "NO" else 1,
                "pk": row["pk"]
            }
            data_list.append(data)

        return data_list

    def update_column_desc(self,table_name:str,col_name,col_type:str, desc:str):
        sql =f"ALTER TABLE  `{table_name}` MODIFY COLUMN {col_name} {col_type} COMMENT '{desc}';"
        # print(sql)
        cur = self.conn.cursor()
        # 执行SQL查询
        cur.execute(sql)
        cur.close()
        return {"code":1,"msg":"update ok"}

    def __del__(self):
        self.conn.close()